package common.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.dao.JdbcDAO;
import common.pojo.HotelOrder;

@Scope("prototype")
@Repository
public class HotelOrderDAO extends JdbcDAO {

	/**
	 * 获取所有组信息
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<HotelOrder> find(String where){
		String sql = "select * from hotelOrder where "+where+" order by orderTime desc";
		return getList(sql,null,new HotelOrderMapper());
	}
	
	/**
	 * 分页查询
	 * @param start
	 * @param limit
	 * @param orderby
	 * @param desc
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<HotelOrder> findList(int start, int limit,String orderbys, String where){
		String sql ="SELECT * FROM "+ 
					"(SELECT *, ROW_NUMBER() OVER (ORDER BY "+orderbys+") as rowNum FROM hotelOrder  where "+where+")" +
					" AS T "+ 
					"WHERE T.RowNum>"+start+ "and T.RowNum<="+(start+limit);
		return getList(sql,null,new HotelOrderMapper());
	}
	/**
	 * 取得热销房型，统计模块
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<HotelOrder> findSearchList(String where){
		 class Mapper implements RowMapper {

				public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
					HotelOrder hotelOrder = new HotelOrder();
					hotelOrder.setOrdernum(rs.getInt(1));
					hotelOrder.setRoomStyleId(rs.getInt(2));
					hotelOrder.setTotalMoney(rs.getDouble(3));
					hotelOrder.setRoomName(rs.getString(4));
					return hotelOrder;
				}
			}
		 String sql = "SELECT  * from " +
		 "(SELECT ordernum,id as roomStyleId,totalMoney,name as roomName from roomStyle  " +
		 "left join (SELECT sum(totalMoney) as totalMoney,count(*) as ordernum,roomStyleId FROM hotelOrder where" +
		 where+" GROUP BY roomStyleId) t   on t.roomStyleId=roomStyle.id   )  tt order by ordernum desc,totalMoney  desc";
		return getList(sql,null,new Mapper());
	}
	@SuppressWarnings("unchecked")
	public List<HotelOrder> findHotList(String where,String where2,String count){
		 class Mapper implements RowMapper {

				public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
					HotelOrder hotelOrder = new HotelOrder();
					hotelOrder.setOrdernum(rs.getInt(1));
					hotelOrder.setRoomStyleId(rs.getInt(2));
					hotelOrder.setTotalMoney(rs.getDouble(3));
					hotelOrder.setRoomName(rs.getString(4));
					hotelOrder.setPrice(rs.getDouble(5));
					hotelOrder.setPicUrl(rs.getString(6));
					return hotelOrder;
				}
			}
		 String sql = "SELECT "+count+" * from " +
		 "(SELECT ordernum,id as roomStyleId,totalMoney,name as roomName ,price,picUrl from roomStyle  " +
		 "left join (SELECT sum(totalMoney) as totalMoney,count(*) as ordernum,roomStyleId FROM hotelOrder" +
		 where+" GROUP BY roomStyleId) t   on t.roomStyleId=roomStyle.id   )  tt "+where2+"order by ordernum desc,totalMoney  desc";
		return getList(sql,null,new Mapper());
	}
	
	/**
	 * 取得数据总数
	 * @return
	 */
	
	public int getCount(String where){
		return getCount("select count(*) from hotelOrder   where "+where,null);
	}
	/**
	 * 获取满足条件的房间剩余数量
	 */	
	public int getCountOrdered(String where){
		return getCount("select sum(totalNum) from hotelOrder   where "+where,null);
	}
	
	public HotelOrder findInfoById(int id){
		String sql ="SELECT * FROM hotelOrder  where id ="+id;
		return (HotelOrder) getObject(sql,null,new HotelOrderMapper());
	}
	
	public HotelOrder findByCode(String orderCode) {
		 class Mapper implements RowMapper {

				public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
					HotelOrder hotelOrder = new HotelOrder();
					hotelOrder.setId(rs.getInt(1));				
					hotelOrder.setTotalMoney(rs.getDouble(2));
					hotelOrder.setCode(rs.getString(3));
					return hotelOrder;
				}
			}
		String sql ="SELECT id,totalMoney,code FROM hotelOrder  where code ="+orderCode;
		return (HotelOrder) getObject(sql,null,new Mapper());
	}

	public void verify(int id){
		update("update hotelOrder set verify =1 where id="+id);
	}
	
	public void save(HotelOrder hotelOrder){
		String sql ="INSERT INTO hotelOrder (memberId,customerName,roomStyleId,code,checkinTime,checkoutTime,totalMoney" +
				",totalNum,phone,identityCardNum,roomName,orderTime,verify,tradeStatus,isReview) VALUES (?,?,?,?,?,?,?,?,?,?,?,getdate(),0,1,0)";
		save(sql, new Object[] {hotelOrder.getMemberId(),hotelOrder.getCustomerName(),
				hotelOrder.getRoomStyleId(),hotelOrder.getCode(),hotelOrder.getCheckinTime(),
				hotelOrder.getCheckoutTime(),hotelOrder.getTotalMoney(),hotelOrder.getTotalNum(),
				hotelOrder.getPhone(),hotelOrder.getIdentityCardNum(),hotelOrder.getRoomName()});
	}
	/**
	 * 更新数据库
	 * @param 
	 * @return
	 */
	
	public void update(HotelOrder hotelOrder){
		String sql="update hotelOrder set totalNum="+hotelOrder.getTotalNum()+",totalMoney="+hotelOrder.getTotalMoney()+
		",customerName='"+hotelOrder.getCustomerName()+"',"+
			"phone='"+hotelOrder.getPhone()+"',identityCardNum='"+hotelOrder.getIdentityCardNum()+"' where id="+hotelOrder.getId();
		update(sql);
	}
	
	public void updateState(int id, int state)
	{	
		SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd 12:00:00");
		String sql;
		if(state!=4)
			sql = "update hotelOrder set tradeStatus=" + state + ", verify =1 where id="+ id;
		else 
			sql = "update hotelOrder set tradeStatus=" + state + ", verify =1,checkoutTime='"+df.format(new Date())+"' where id="+ id;
		update(sql);
	}
	
	public void updateReview(int orderid) {
		String sql = "update hotelOrder set isReview=1 where id="+ orderid;
		update(sql);
	}
	/**
	 * 删除
	 * @param id
	 * @return
	 */
	public boolean delete(int id){
		String sql ="delete from hotelOrder where id = "+id;
		delete(sql);
		return true;
	}
	
	/**
	 * 映射私有类
	 * @author pfp
	 *
	 */
	private static final class HotelOrderMapper implements RowMapper {

		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			HotelOrder hotelOrder = new HotelOrder();
			hotelOrder.setId(rs.getInt(1));
			hotelOrder.setMemberId(rs.getInt(2));
			hotelOrder.setCustomerName(rs.getString(3));
			hotelOrder.setRoomStyleId(rs.getInt(4));
			hotelOrder.setCode(rs.getString(5));
			hotelOrder.setCheckinTime(rs.getTimestamp(6));
			hotelOrder.setCheckoutTime(rs.getTimestamp(7));
			hotelOrder.setTotalMoney(rs.getDouble(8));
			hotelOrder.setTotalNum(rs.getInt(9));
			hotelOrder.setTradeStatus(rs.getInt(10));
			hotelOrder.setPhone(rs.getString(11));
			hotelOrder.setIdentityCardNum(rs.getString(12));
			hotelOrder.setOrderTime(rs.getTimestamp(13));
			hotelOrder.setVerify(rs.getBoolean(14));
			hotelOrder.setRoomName(rs.getString(15));
			hotelOrder.setIsReview(rs.getBoolean(16));
			return hotelOrder;
		}
	}

}
